import datetime
import os

from openpyxl import load_workbook
from openpyxl.styles import Border, Side

data = {}
cell_titles = {}
must_titles = ["体温℃", "现居住地", "幼儿是否入园", "是否居家"]


class Sheet:
    work_day = False
    row_count = 0
    column_count = 0
    cells = {}


def is_work_day(dt):
    year = datetime.datetime.now().year
    mon = dt[0:dt.find("月")]
    day = dt[dt.find("月") + 1:dt.find("日")]

    date = str(year) + "-" + mon + "-" + day
    time = datetime.datetime.strptime(date, '%Y-%m-%d')  # string转换为datetime
    week = time.isoweekday()
    if week == 6 or week == 7:
        return True
    return False


def set_excel_config(out_folder, f_name1, f_name, fext):
    wb = load_workbook(filename=f_name)
    sheet = wb['Sheet1']

    firstRow = 0
    for i in range(1, 5):
        for j in range(1, 5):
            if str(sheet.cell(i, j).value).replace("\n", "").replace("\t", "").replace(" ", "") == "幼儿姓名":
                firstRow = i

    maxcolnums = sheet.max_column + 1
    maxrows = sheet.max_row + 1
    print(maxcolnums)
    for i in range(5, sheet.max_column + 1):
        if str(sheet.cell(firstRow, i).value).replace("\n", "").replace("\t", "").replace(" ", "") == "None" or str(
                sheet.cell(firstRow, i).value).replace("\n", "").replace("\t", "").replace(" ", "") == "":
            print(str(i) + "|||||||||" + str(sheet.cell(firstRow, i).value))
            maxcolnums = i - 1
            break
        else:
            print(str(i) + "!!!!!!!!!" + str(sheet.cell(firstRow, i).value))
            maxcolnums = i

    for i in range(5, sheet.max_row + 1):
        if str(sheet.cell(i, 1).value).replace("\n", "").replace("\t", "").replace(" ", "") == "None" or str(
                sheet.cell(i, 1).value).replace("\n", "").replace("\t", "").replace(" ", "") == "":
            maxrows = i - 1
            break
        else:
            maxrows = i



    width = 130 / maxcolnums
    print(fname)
    print(str(maxrows) + "|||")
    print(str(maxcolnums) + "||||")
    for i in range(1, maxcolnums):
        sheet.column_dimensions[str(chr(i + 97))].width = width

    for i in range(1, sheet.max_column + 1):
        for j in range(1, sheet.max_row + 1):
            sheet.cell(j, i).border = None
            if j <= maxrows and i <= maxcolnums:
                sheet.cell(j, i).border = Border(left=Side(style='thin'), right=Side(style='thin'),

                                                 top=Side(style='thin'),

                                                 bottom=Side(style='thin'))
            else:
                sheet.cell(j, i).value = None

    ws = wb.active
    ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE
    ws.page_setup.paperSize = ws.PAPERSIZE_A4
    out_path = out_folder + "/" + f_name1 + fext
    wb.save(out_path)
    return out_path


def save_data(path, fname1, isWorkDay):
    # print("保存" + fname1)
    cell_titles[fname1] = []
    wb = load_workbook(filename=path)
    sheet = wb['Sheet1']
    m_sheet = Sheet()
    m_sheet.cells = {}
    m_sheet.work_day = isWorkDay
    m_sheet.row_count = sheet.max_row
    m_sheet.column_count = sheet.max_column
    cell_names = {}
    for r in sheet.rows:
        name = ""
        if r[0].row >= 4:
            name = str(r[0].value) + "||0:" + str(r[0].row)
        cell = {}
        for c in r:
            if c.row == 2:
                key = str(c.column) + ":" + str(c.row)
                cell_name = str(c.value).replace("\n", "").replace("\t", "").replace(" ", "")
                if cell_name != "None":
                    cell_titles[fname1].append(cell_name)
                    cell_names[key] = cell_name
            if c.row == 1 or c.row == 3:
                continue
            if c.value is None:
                continue
            if c.column == 1 and c.value == "\n":
                continue
            if c.row > 3 and str(name) != "None":
                value = c.value
                cellkey = str(c.column) + ":2"
                if cellkey in cell_names:
                    cellname = str(cell_names[cellkey]) + "||" + str(c.column) + ":" + str(c.row)
                    cell[cellname] = value
                    m_sheet.cells[name] = cell
    data[fname1] = m_sheet


def validate_data(path, f_name):
    skey = f_name[0:f_name.find(".")]
    # print("开始检查" + skey)
    m_sheet = data[skey]
    to_be_completion_cells = {}
    for u in m_sheet.cells:
        m_cell = m_sheet.cells[u]
        if len(m_cell) < m_sheet.column_count:
            to_be_completion_cells[u] = m_cell
            # if len(m_cell) == 1:
            #     print("没有填写" + "||" + str(u) + "||" + str(m_cell))
            # else:
            #     print("信息填写不全" + "||" + str(u) + "||" + str(m_cell))
    find_and_completion_into(path, skey, to_be_completion_cells, m_sheet.column_count, m_sheet.work_day)


def find_and_completion_into(path, key, to_be_completion_cells, column_count, work_day):
    completion_cells = {}
    to_be_completion_cells_titles = {}
    for tbkey in to_be_completion_cells:
        to_be_completion_cells_titles[tbkey] = []
        # print("信息填写不全" + "||" + str(tbkey) + "||")
        tbcells = to_be_completion_cells[tbkey]
        for uk in data:
            completion_cells[tbkey] = {}
            temp_sheet = data[uk]
            if tbkey not in temp_sheet.cells:
                # print(str(uk) + "没有找到" + str(tbkey))
                continue
            temp_cells = temp_sheet.cells[tbkey]
            if work_day != temp_sheet.work_day:
                # print(str(uk) + "是否为workday不一致" + str(tbkey))
                continue
            if temp_sheet.column_count != column_count:
                # print(str(uk) + "列数不一致" + str(tbkey))
                continue

            if len(temp_cells) != column_count:
                # print(str(uk) + "填写也不全" + str(tbkey))
                continue

            for tc in temp_cells:
                if tc not in tbcells:
                    completion_cells[tbkey][tc] = temp_cells[tc]
                    tbcells[tc] = temp_cells[tc]
            # print("使用" + uk + "补全" + str(tbkey) + "||" + str(tbcells))
            break
    # print("已补全" + str(completion_cells))
    wb = load_workbook(filename=path)
    sheet = wb['Sheet1']
    for k in completion_cells:
        c_cells = completion_cells[k]
        if len(c_cells) == 0:
            continue
        for ck in c_cells:
            cks = str(ck).split("||")
            if len(cks) == 2:
                ckss = cks[1].split(":")
                if len(ckss) == 2:
                    column = ckss[0]
                    row = ckss[1]
                    sheet.cell(row=int(row), column=int(column), value=str(c_cells[ck]))
    wb.save(path)

    for k in to_be_completion_cells:
        c_cells = to_be_completion_cells[k]
        if len(c_cells) == 0:
            continue
        for ck in c_cells:
            cks = str(ck).split("||")
            if len(cks) == 2:
                to_be_completion_cells_titles[k].append(str(cks[0]))

    for tbkey in to_be_completion_cells:
        cells = data[key].cells[tbkey]
        if len(cells) == column_count:
            # print("成功修复" + str(cells))
            pass
        else:
            uncompletion_title = []
            for tk in cell_titles[key]:
                if tk not in to_be_completion_cells_titles[tbkey] and tk in must_titles:
                    uncompletion_title.append(tk)
            if len(uncompletion_title) > 0:
                print(str(key) + "未成功修复" + str(tbkey) + str(uncompletion_title))

    # wb = load_workbook(filename=path)
    # sheet = wb['Sheet1']
    #
    # sheet.cells[]


if __name__ == '__main__':
    # try:
    print("开始修复格式...")
    currFolder = os.getcwd()
    outFolder = currFolder + "\\out"
    if not os.path.exists(outFolder):
        os.makedirs(outFolder)
    file_name = os.listdir(currFolder)
    for f in file_name:
        fname = currFolder + "/" + f
        fext = fname[-5:]
        fname1 = f[0:f.find("日") + 1]
        if fext == ".xlsx":
            outPath = set_excel_config(outFolder, fname1, fname, fext)
            # print("已经导出" + outPath)
            isWorkDay = is_work_day(fname1)
            save_data(outPath, fname1, isWorkDay)
    print("导出成功,开始检查数据")
    out_filenames = os.listdir(outFolder)
    for f in out_filenames:
        validate_data(outFolder + "/" + f, f)
    print("修复数据完成。")
    # except Exception as ex:
    print("导出失败，请关闭打开的excel并重试")
# input()
